import win32com.client,sqlite3
from datetime import datetime
import time

# o = win32com.client.Dispatch("Object.Name")
# o.Method()
# o.property = "New Value"
# print o.property

# o = win32com.client.Dispatch("Excel.Application")
# o.Visible = 1
# o.Workbooks.Add()
# o.Cells(1,1).Value = "Hello"
conn = win32com.client.Dispatch(r'ADODB.Connection')
# db='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nd.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";'

db='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nd.xls;Extended Properties=Excel 8.0;'
conn.Open(db)

# rs,success=conn.Execute('select * from [lkt$]')
# while not rs.EOF:
  # print rs.Fields('ndip').Value
  # rs.MoveNext()
 
  
rs = win32com.client.Dispatch(r'ADODB.Recordset')
sql="select * from [HDMI$]"
rs.Open(sql,conn,1,1)
# flds_dict = {}
# for x in range(rs.Fields.Count):
  # flds_dict[x] = rs.Fields.Item(x).Name
  # print rs.Fields.Item(x).Name
conn1=sqlite3.connect(r'lkt.db3')
conn1.text_factory = str
while not rs.EOF:
  date=str(rs.Fields("date").Value)
  ndip=rs.Fields("ndip").Value
  content=rs.Fields("content").Value
  cpemac=rs.Fields("cpemac").Value
  old=rs.Fields("old").Value
  new=rs.Fields("new").Value
  refer=rs.Fields("refer").Value
  memo=rs.Fields("memo").Value
  miss=rs.Fields("miss").Value
  if miss=="miss":
    bmiss=1
    ioperate=5
  else:
    bmiss=0
    ioperate=2
  # date=datetime.strptime(date,'%m/%d/%y %H:%M:%S')
  date=time.strptime(date,'%m/%d/%y %H:%M:%S')
  date=time.strftime("%Y-%m-%d %H:%M:%S",date)
  # date=time.strptime(date,'%Y-%m-%d')
  # print date,ndip,ems,oldver,newver,operate,emsver,memo
  conn1.execute("insert into faultrecord(ndip,dtDate,sContent,sMemo,sOldSoftVer,sNewSoftVer,iType,iOperate,sCpeMac,iUser,bHdmi,sRefer,bMiss) values(?,?,?,?,?,?,?,?,?,?,?,?,?)",(ndip,date,content,memo,old,new,1,ioperate,cpemac,2,1,refer,bmiss))
  rs.MoveNext()
  
conn1.commit()
conn1.close()

rs.Close()
conn.Close()

